package com.hele.sts.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import org.joda.time.DateTime;
import org.joda.time.format.DateTimeFormat;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.hele.sts.exception.BusinessException;
import com.hele.sts.pub.ConnectDB;
import com.hele.sts.pub.SpringContextHolder;

public class QyjnmlbDao {
	private static final SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

	static Logger logger = LoggerFactory.getLogger(QyjnmlbDao.class);
	
	public static int count(String nbxh)
	  {
	    Connection conn = null;
	    
	    PreparedStatement pstmt = null;
	    ResultSet rs = null;
	    String sql = null;
	    ConnectDB connsyb = null;
	    sql = "SELECT count(*) FROM da_qyjnmlb WHERE nbxh=? ";
	    
	    logger.debug(sql);
	    try
	    {
	      connsyb = SpringContextHolder.getBean(ConnectDB.class);
	      conn = connsyb.getConnection();
	      pstmt = conn.prepareStatement(sql);
	      int i = 1;
	     
	      if ((nbxh != null) && (nbxh.length() > 0)) {
	        pstmt.setString(i++, nbxh);
	      }
	     
	      rs = pstmt.executeQuery();
	      if (rs.next()) {
	        return rs.getInt(1);
	      }
	    }
	    catch (SQLException e)
	    {
	      logger.info("db error", e);
	    }
	    finally
	    {
	      DbClose.freePreparedStatement(pstmt);
	      DbClose.freeConnection(conn);
	      DbClose.freeResultSet(rs);
	    }
	    return 0;
	  }
	
	public static List load(String nbxh, int page, int pageSize)
	  {
	    Connection conn = null;
	    PreparedStatement pstmt = null;
	    ResultSet rs = null;
	    String sql = null;
	    ConnectDB connsyb = null;
	    //sql = "SELECT * FROM (SELECT rownum r, a.*,b.djjgjc as djjgmc,c.content as qyzt FROM hz_qyhznr a,bm_djjg b,bm_qyzt c WHERE a.djjg=b.code and a.zt=c.code ";
	    sql = "SELECT * FROM (SELECT rownum r, a.*,b.content as jnmlmc,nvl(c.djjgjc,a.djjg) djjgmc FROM da_qyjnmlb a,da_jnmlmcb b,bm_djjg c WHERE a.nbxh=? and a.jnmlbh=b.code and a.djjg=c.code(+) ";
	    
	    logger.debug(
	      pageSize + "page" + page);
	    if (pageSize > 1) {
	      sql = 
	        sql + " AND rownum <=? * ?  order by a.jnmlbh,a.inmlbh)t WHERE t.r > (? - 1) * ? ";
	    }
	    logger.info("load sql:" + sql);
	    try
	    {
	      connsyb = SpringContextHolder.getBean(ConnectDB.class);
	      conn = connsyb.getConnection();
	      pstmt = conn.prepareStatement(sql);
	      int i = 1;
	      pstmt.setString(i++, nbxh);
	      
	      if (pageSize > 1)
	      {
	        pstmt.setInt(i++, page);
	        pstmt.setInt(i++, pageSize);
	        pstmt.setInt(i++, page);
	        pstmt.setInt(i++, pageSize);
	      }
	      rs = pstmt.executeQuery();
	      List list = new ArrayList();
	      while (rs.next()) {
	        list.add(rsToBean(rs));
	      }
	      logger.debug(
	        list.size() + "--");
	      List localList1 = list;
	      return localList1;
	    }
	    catch (SQLException e)
	    {
	      logger.info("db error", e);
	    }
	    finally
	    {
	      DbClose.freePreparedStatement(pstmt);
	      DbClose.freeConnection(conn);
	      DbClose.freeResultSet(rs);
	    }
	    
	    return null;
	  }

	private static Qyjnmlb rsToBean(ResultSet rs) throws SQLException {
		Qyjnmlb qyjnmlb = new Qyjnmlb();
		qyjnmlb.setNbxh(rs.getString("nbxh"));
		qyjnmlb.setJnmlbh(rs.getInt("jnmlbh"));
		qyjnmlb.setJnmlmc(rs.getString("jnmlmc"));
		qyjnmlb.setInmlbh(rs.getInt("inmlbh"));
		qyjnmlb.setClmlmc(rs.getString("clmlmc"));
		qyjnmlb.setInmlqx(rs.getString("inmlqx"));
		qyjnmlb.setInmlys(rs.getInt("inmlys"));
		qyjnmlb.setWjljmc(rs.getString("wjljmc"));
		qyjnmlb.setCzbz(rs.getString("czbz"));
		qyjnmlb.setSmbz(rs.getString("smbz"));
		qyjnmlb.setSmsj(new DateTime(rs.getDate("smsj")).toString("yyyy-MM-dd"));
		qyjnmlb.setWjdx(rs.getLong("wjdx"));
		qyjnmlb.setDjjgmc(rs.getString("djjgmc"));
		return qyjnmlb;
	}
	
	public static List<Wjxx> getWjxxList(String nbxh,String jnmlbh) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ConnectDB connsyb = null;
		ResultSet rs = null;
		List<Wjxx> list = new ArrayList<Wjxx>();
		String lsSql = "select nbxh,jnmlbh,inmlbh,wjljmc,inmlqx,nvl(czbz,'3') as czbz FROM DA_QYJNMLB where nbxh=? and jnmlbh=? and (czbz!='0' or czbz is null) order by inmlbh";
		logger.debug(lsSql);
		connsyb = SpringContextHolder.getBean(ConnectDB.class);

		try {
			conn = connsyb.getConnection();
			pstmt = conn.prepareStatement(lsSql);
			pstmt.setString(1, nbxh);
			pstmt.setString(2, jnmlbh);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				Wjxx wjxx = new Wjxx();
				String wjljmc = rs.getString("wjljmc");
				String czbz = rs.getString("czbz");
				if("2".equals(czbz)){
					wjljmc = getConvertedFile(wjljmc);
				}
				wjxx.setWjljmc(wjljmc);
				wjxx.setJnmlbh(rs.getString("jnmlbh"));
				wjxx.setInmlbh(rs.getString("inmlbh"));
				wjxx.setNbxh(rs.getString("nbxh"));
				wjxx.setCxqx(rs.getString("inmlqx"));
				list.add(wjxx);
			}
			return list;
		} catch (SQLException e) {
			logger.info("db error", e);
		} finally {
			DbClose.freeResultSet(rs);
			DbClose.freePreparedStatement(pstmt);
			DbClose.freeConnection(conn);
		}

		return list;
	}
	
	private static String getConvertedFile(String filePath) {
		String a = filePath.substring(0,filePath.lastIndexOf("\\")+1);
		String newFile = "new-"+filePath.substring(filePath.lastIndexOf("\\")+1);
		return a+newFile;
	}

	public static Datjxx getDatjxx(String djjg, String startDate, String endDate) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ConnectDB connsyb = null;
		ResultSet rs = null;
		Datjxx tjxx = new Datjxx();
		String lsSql = "select count(distinct nbxh) as totalEnt,count(distinct nbxh||jnmlbh) totalJnml,count(1) totalInml,nvl(sum(inmlys),0) totalDocPage,nvl(sum(decode(wjdx,-1,0,wjdx)),0) totalWjdx from da_qyjnmlb a where djjg like ? ";
		//xuning add 20160922
				if (isValidDate(startDate)||isValidDate(endDate)){
					lsSql = lsSql + " AND EXISTS (select 1 from da_qyzfjzmlb b where a.nbxh=b.nbxh and a.jnmlbh=b.jnmlbh ";
					if(startDate!=null){
						lsSql+=" and b.jnmldjrq >=to_date('"+startDate+"','yyyy-MM-dd')";
					}
					if(endDate!=null){
						lsSql+=" and b.jnmldjrq <to_date('"+endDate+"','yyyy-MM-dd')+1";
					}
					lsSql+=")";
				}
		logger.debug(lsSql);
		connsyb = SpringContextHolder.getBean(ConnectDB.class);

		try {
			conn = connsyb.getConnection();
			pstmt = conn.prepareStatement(lsSql);
			pstmt.setString(1, djjg+"%");
			rs = pstmt.executeQuery();
			if (rs.next()) {
				tjxx.setTotalEnt(rs.getInt(1));
				tjxx.setTotalJnml(rs.getInt(2));
				tjxx.setTotalInml(rs.getInt(3));
				tjxx.setTotalDocPage(rs.getInt(4));
				tjxx.setTotalDocSize(rs.getLong(5));
			}
			return tjxx;
		} catch (SQLException e) {
			logger.info("db error", e);
		} finally {
			DbClose.freePreparedStatement(pstmt);
			DbClose.freeConnection(conn);
		}

		return null;
	}

	public static void resetSmbz(String nbxh, String jnmlbh, String inmlbh) {
		// TODO Auto-generated method stub
		Connection conn = null;
		PreparedStatement pstmt = null;
		ConnectDB connsyb = null;
		ResultSet rs = null;
		String lsSql = "update da_qyjnmlb set smbz='0' where nbxh=? and jnmlbh=? and inmlbh=?";
		logger.debug(lsSql);
		connsyb = SpringContextHolder.getBean(ConnectDB.class);

		try {
			conn = connsyb.getConnection();
			pstmt = conn.prepareStatement(lsSql);
			pstmt.setString(1, nbxh);
			pstmt.setString(2, jnmlbh);
			pstmt.setString(3, inmlbh);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			logger.info("db error", e);
			throw new BusinessException("扫描标志重置失败");
		} finally {
			DbClose.freePreparedStatement(pstmt);
			DbClose.freeConnection(conn);
		}

	}
	
	private static boolean isValidDate(String date) {
		if(date==null) return false;
		try {
			sdf.parse(date);
			return true;
		} catch (ParseException e) {
			logger.debug(date+" 是无效日期");
		}
		return false;
	}
}
